﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DAO
{
    public class XuLyKhachHang
    {
        // tim ds khach hang
        public static DataTable TimDSKhachHang()
        {
            // lop DataProvider
            DAO.CDataProvider reader = new DAO.CDataProvider();
            // chuoi SELECT
            String sql = " SELECT * FROM KHACHHANG ";

            DataTable dt = new DataTable();
            try
            {
                // Bang data se duoc nhap
                dt = reader.DocDuLieu(sql);


            }
            catch (System.Exception)
            {

            }


            return dt;
        }
        // tim 1 khach hang theo maKH
        public static DTO.CKHACHHANG TimKhachHangTheoMaKH(int MaKhachH)
        {
            DTO.CKHACHHANG kh = new DTO.CKHACHHANG();
          //  String ma = Convert.ToString(MaKhachH
            // lop DataProvider
            DAO.CDataProvider reader = new DAO.CDataProvider();
            // chuoi SELECT
            String sql = " SELECT * FROM KHACHHANG " +
            " WHERE MaKH = " + MaKhachH+ " ";

            try
            {
                // Bang data se duoc nhap
                DataTable dt = reader.DocDuLieu(sql);

                // Neu ton tai nhan vien nay
                if (dt.Rows.Count == 1)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        kh.HoTen = dr["HoTen"].ToString();
                        kh.MaKH = int.Parse(dr["MaKH"].ToString());
                        kh.Tuoi = int.Parse(dr["Tuoi"].ToString());
                        kh.DiaChi = dr["DiaChi"].ToString();
                        kh.CMND = dr["CMND"].ToString();
                    }
                }
                // nhan vien nay ko ton tai
                else
                {
                    kh.HoTen = "khong tim thay";
                    kh.MaKH = 0;
                    kh.Tuoi = 0;
                    kh.DiaChi = "khong tim thay";
                    kh.CMND = " ";
                }

            }
            catch (System.Exception )
            {
            	
            }
            
            return kh;
        }
        // tim 1 khach hang theo so CMND
        public static DTO.CKHACHHANG TimKhachHangTheoSoCMND(String SoCMND)
        {
            DTO.CKHACHHANG kh = new DTO.CKHACHHANG();
            // lop DataProvider
            DAO.CDataProvider reader = new DAO.CDataProvider();
            // chuoi SELECT
            String sql = " SELECT * FROM KHACHHANG " +
            " WHERE CMND = '" + SoCMND.ToString() + "' ";

            try
            {
                // Bang data se duoc nhap
                DataTable dt = reader.DocDuLieu(sql);

                // Neu ton tai nhan vien nay
                if (dt.Rows.Count == 1)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        kh.HoTen = dr["HoTen"].ToString();
                        kh.MaKH = int.Parse(dr["MaKH"].ToString());
                        kh.Tuoi = int.Parse(dr["Tuoi"].ToString());
                        kh.DiaChi = dr["DiaChi"].ToString();
                        kh.CMND = dr["CMND"].ToString();
                    }
                }
                // nhan vien nay ko ton tai
                else
                {
                    kh.HoTen = "khong tim thay";
                    kh.MaKH = 0;
                    kh.Tuoi = 0;
                    kh.DiaChi = "khong tim thay";
                    kh.CMND = " ";
                }

            }
            catch (System.Exception)
            {

            }

            return kh;
        }
        // tim 1 khach hang theo HoTen
        public static DTO.CKHACHHANG TimKhachHangTheoHoTen(String Ten)
        {
            DTO.CKHACHHANG kh = new DTO.CKHACHHANG();
            // lop DataProvider
            DAO.CDataProvider reader = new DAO.CDataProvider();
            // chuoi SELECT
            String sql = " SELECT * FROM KHACHHANG " +
            " WHERE HoTen="  + "'" + Ten.ToString()+"'";

            try
            {
                // Bang data se duoc nhap
                DataTable dt = reader.DocDuLieu(sql);

                // Neu ton tai nhan vien nay
                if (dt.Rows.Count >= 1)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        kh.HoTen = dr["HoTen"].ToString();
                        kh.MaKH = int.Parse(dr["MaKH"].ToString());
                        kh.Tuoi = int.Parse(dr["Tuoi"].ToString());
                        kh.DiaChi = dr["DiaChi"].ToString();
                        kh.CMND = dr["CMND"].ToString();
                    }
                }
                // nhan vien nay ko ton tai
                else
                {
                    kh.HoTen = "khong tim thay";
                    kh.MaKH = 0;
                    kh.Tuoi = 0;
                    kh.DiaChi = "khong tim thay";
                    kh.CMND = " ";
                }

            }
            catch (System.Exception)
            {

            }

            return kh;
        }
        // tim 1 khach hang theo dia chi
        public static DTO.CKHACHHANG TimKhachHangTheoDiaChi(String DiachiKH)
        {
            DTO.CKHACHHANG kh = new DTO.CKHACHHANG();
            // lop DataProvider
            DAO.CDataProvider reader = new DAO.CDataProvider();
            // chuoi SELECT
            String sql = " SELECT * FROM KHACHHANG " +
            " WHERE DiaChi LIKE '%" + DiachiKH.ToString() + "%' OR DiaChi LIKE '" + DiachiKH.ToString() + "%' OR DiaChi LIKE '%" + DiachiKH.ToString() + "' ";

            try
            {
                // Bang data se duoc nhap
                DataTable dt = reader.DocDuLieu(sql);

                // Neu ton tai nhan vien nay
                if (dt.Rows.Count == 1)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        kh.HoTen = dr["HoTen"].ToString();
                        kh.MaKH = int.Parse(dr["MaKH"].ToString());
                        kh.Tuoi = int.Parse(dr["Tuoi"].ToString());
                        kh.DiaChi = dr["DiaChi"].ToString();
                        kh.CMND = dr["CMND"].ToString();
                    }
                }
                // nhan vien nay ko ton tai
                else
                {
                    kh.HoTen = "khong tim thay";
                    kh.MaKH = 0;
                    kh.Tuoi = 0;
                    kh.DiaChi = "khong tim thay";
                    kh.CMND = " ";
                }

            }
            catch (System.Exception)
            {

            }

            return kh;
        }
        // them 1 khach hang
        public static Boolean ThemKhachHang(DTO.CKHACHHANG kh)
        {
            DAO.CDataProvider writer = new DAO.CDataProvider();

            // Cau lenh SQL
            String insertSQL = " INSERT INTO KHACHHANG(HoTen,Tuoi,DiaChi,CMND) "
                + " VALUES('" + kh.HoTen.ToString() + "',"
                + kh.Tuoi + ","
                + "'" + kh.DiaChi.ToString() + "',"
                + "'" + kh.CMND.ToString() + "') ";

            // Thuc thi cau lenh insert
            if (writer.ThucThiDuLieu(insertSQL))
                return true;
            else
                return false;
        }
        // sua thong tin 1 khach hang
        public static Boolean SuaTTKhachHang(DTO.CKHACHHANG kh, int MaKhachH)
        {
            DAO.CDataProvider writer = new DAO.CDataProvider();

            // Cau lenh SQL
            String updateSQL = "UPDATE KHACHHANG "
                + " SET HoTen = '" + kh.HoTen.ToString() + "' , "
                + " Tuoi = " + kh.Tuoi + " , "
                + " DiaChi = '" + kh.DiaChi.ToString() + "' , "
                + " CMND = '" + kh.CMND.ToString() + "' "
                + " WHERE MaKH = " + MaKhachH + " ";

            // Thuc thi cau lenh insert
            if (writer.ThucThiDuLieu(updateSQL))
                return true;
            else
                return false;
        }
        // xoa 1 khach hang
        public static Boolean XoaKhachHang(int MaKhachH)
        {
            DAO.CDataProvider writer = new DAO.CDataProvider();

            // Cau lenh SQL
            String deleteSQL = " DELETE FROM KHACHHANG "
                + " WHERE MaKH = " + MaKhachH + " ";

            // Thuc thi cau lenh insert
            if (writer.ThucThiDuLieu(deleteSQL))
                return true;
            else
                return false;
        }
    }
}
